package com.ziyueyuwen.xiniao.util;

import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 读取Excel文件
 *
 * @author jun 2018/10/25
 */
public class ExcelUtil {
    private static Logger logger = Logger.getLogger(ExcelUtil.class);

    public static List<JSONObject> readExcel(InputStream is) throws Exception {
        return readExcel(is, 0, 0, 0);
    }

    public static List<JSONObject> readExcel(InputStream is, int sheetNum) throws Exception {
        return readExcel(is, sheetNum, 0, 0);
    }

    public static List<JSONObject> readExcel(InputStream is, int sheetNum, int rowStartNum, int cellStartNum) throws Exception {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetNum);
        if (xssfSheet == null) {
            throw new Exception("工作表不存在!");
        }
        List<JSONObject> retList = new ArrayList<>();
        for (int rowNum = rowStartNum; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            if (xssfRow != null) {
                JSONObject obj = new JSONObject();
                if (xssfRow.getCell(cellStartNum) == null || StringUtils.isBlank(getValue(xssfRow.getCell(cellStartNum)))) {
                    break;
                }
                for (int cellNum = cellStartNum; cellNum < xssfRow.getLastCellNum(); cellNum++) {
                    XSSFCell xssfCell = xssfRow.getCell(cellNum);
                    obj.put(String.valueOf(cellNum), (xssfCell == null ? StringUtils.EMPTY : getValue(xssfCell)));
                }
                retList.add(obj);
            }
        }
        return retList;
    }

    public static void writeExcel(List<JSONObject> list, String[] cellTitleArr, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.addHeader("Content-disposition", "attachment;filename=download.xlsx");
        ServletOutputStream outputStream = response.getOutputStream();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        XSSFSheet xssfSheet = xssfWorkbook.createSheet("data");
        XSSFRow xssfRowZero = xssfSheet.createRow(0);
        for (int i = 0; i < cellTitleArr.length; i++) {
            XSSFCell xssfCell = xssfRowZero.createCell(i);
            xssfCell.setCellType(CellType.STRING);
            xssfCell.setCellValue(cellTitleArr[i]);
        }
        for (int i = 0; i < list.size(); i++) {
            XSSFRow xssfRow = xssfSheet.createRow(i + 1);
            for (Map.Entry<String, Object> entry : list.get(i).entrySet()) {
                XSSFCell xssfCell = xssfRow.createCell(Integer.valueOf(entry.getKey()));
                xssfCell.setCellType(CellType.STRING);
                xssfCell.setCellValue(String.valueOf(entry.getValue()));
            }
        }
        xssfWorkbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    private static String getValue(XSSFCell xssfCell) throws Exception {
        String retStr = StringUtils.EMPTY;
        switch (xssfCell.getCellTypeEnum()) {
            case BLANK:
                break;
            case BOOLEAN:
                retStr = String.valueOf(xssfCell.getBooleanCellValue());
                break;
            case ERROR:
                retStr = xssfCell.getErrorCellString();
                break;
            case FORMULA:
                retStr = String.valueOf(xssfCell.getCellFormula());
                break;
            case NUMERIC:
                xssfCell.setCellType(CellType.STRING);
                retStr = xssfCell.getStringCellValue();
                break;
            case STRING:
                retStr = xssfCell.getStringCellValue();
                break;
            default:
                break;
        }
        return retStr;
    }
}
